Since the purpose of the EXECUTE IMMEDIATE statement is to execute dynamic SQL queries - which by definition can contain unexpected
errors - properly handling exceptions becomes critical. Therefore, care should be taken to trap all possible exceptions.
Noncompliant code example
DECLARE
result VARCHAR2(42);
column VARCHAR2(42);
BEGIN
column := 'DUMMY_2';
EXECUTE IMMEDIATE 'SELECT ' || column || ' FROM DUAL' INTO result; -- Non-Compliant
END;
/
Compliant solution
SET SERVEROUTPUT ON
DECLARE
result VARCHAR2(42);
column VARCHAR2(42);
BEGIN
column := 'DUMMY_2';
EXECUTE IMMEDIATE 'SELECT ' || column || ' FROM DUAL' INTO result; -- Compliant
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Execute immediate error: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
/
